﻿/*****************************************************************************/
/* Stored Procedure :: CountGrantsFor                                        */
/*****************************************************************************/
CREATE PROCEDURE [GrantManagement].[CountGrantsFor]
(
	@VolunteerID BIGINT
)
AS  
BEGIN

	DECLARE @Dispersed BIGINT
	SELECT @Dispersed = OID FROM TypesOfActivity WHERE ((Description = 'Dispersed') AND (Active = 1))

	SELECT COUNT(Grants.OID)
	FROM  Grants
	INNER JOIN Cases On Grants.CaseID = Cases.OID
	INNER JOIN People On Cases.ServiceManID = People.OID
	
	-- Don't show dispersed grants if the date they were dispersed is older than yesterday.
	LEFT OUTER JOIN GrantEvents ON GrantEvents.GrantID = Grants.OID
	WHERE ((GrantEvents.OID IS NULL) OR
	       (GrantEvents.ActivityID <> @Dispersed) OR
	       ((GrantEvents.ActivityID = @Dispersed) AND
            (GrantEvents.CreatedOn >= DATEADD(dd, -1, GETDATE()))))
END
